package com.novoda.runbuddy.provider;

import java.util.Calendar;

import com.novoda.runbuddy.util.DBHelperTools;

public class JogsDBSQL {
	public static final int		DATABASE_VERSION							= 1;
//	public static final String	DATABASE_NAME								= "runningBuddyDB1";
	public static final String	DATABASE_NAME								= "data";

	public static final String	JOGGING_ENTRIES_TABLE						= "jog";
	public static final String	ROUTE_TABLE									= "route";
	public static final String	ROUTE_POINT_TABLE							= "routePoint";
	public static final String	TIME_TABLE									= "time";

	public static final String	JOG_ID										= "_id";
	public static final String	JOG_NAME									= "name";

	public static final String	ROUTE_ID									= "_id";
	public static final String	ROUTE_JOGID									= "_jogId";

	public static final String	ROUTEPOINT_POINT_ID							= "_id";
	public static final String	ROUTEPOINT_ROUTE_ID							= "_routePointId";

	public static final String	ROUTEPOINT_LONGITUDE						= "longitude";
	public static final String	ROUTEPOINT_LATITUDE							= "latitude";

	public static final String	TIME_ID										= "_id";
	public static final String	TIME_ROUTE_ID								= "routeId";

	public static final String	TIME_ROUTE_POINT_ID							= "routePointId";
	public static final String	TIME_JOG_ID									= "jogId";
	public static final String	TIME_TIME									= "time";
	public static final String	TIME_LAP									= "lap";

	// All Results come back in the following results table
	public static final int		JOGRESULTS_JOG_ID_COL						= 0;
	public static final int		JOGRESULTS_NAME_COL							= 1;
	public static final int		JOGRESULTS_ROUTE_ID_COL						= 2;
	public static final int		JOGRESULTS_POINT_NO_COL						= 3;
	public static final int		JOGRESULTS_LONGITUDE_COL					= 4;
	public static final int		JOGRESULTS_LATITUDE_COL						= 5;
	public static final int		JOGRESULTS_TIME_COL							= 6;
	public static final int		JOGRESULTS_LAP_COL							= 7;

	/*
	 * Database Layout
	 * 
	 * jog - Stores each jog entry route - Each jog has a route associated with
	 * it. routePoint - Each route is made from stored points from the journey
	 * time - Times are are associated with points on route during a jog
	 * 
	 */

	public static final String	CREATE_JOG_TABLE							= "CREATE TABLE jog (_id INTEGER PRIMARY KEY, name TEXT);";

	public static final String	CREATE_ROUTE_TABLE							= "CREATE TABLE route (_id INTEGER PRIMARY KEY, _jogId INTEGER);";

	public static final String	CREATE_ROUTE_POINT_TABLE					= "CREATE TABLE routePoint (_id INTEGER PRIMARY KEY, pointNo NUMERIC, _routeId INTEGER, latitude NUMERIC, longitude NUMERIC);";

	public static final String	CREATE_TIME_TABLE							= "CREATE TABLE time (_id INTEGER PRIMARY KEY, jogId NUMERIC, routePointId NUMERIC, routeId NUMERIC, lap BOOLEAN, time DATETIME);";

	// END-DB STRUCTURE

	public static String[]		COLUMNS_IN_JOG_ENTRY						= new String[] { JOG_ID, JOG_NAME };

	public static String[]		COLUMNS_IN_ROUTE_ENTRY						= new String[] { ROUTE_ID, ROUTE_JOGID };

	public static String[]		COLUMNS_IN_ROUTEPOINT_ENTRY					= new String[] { ROUTEPOINT_POINT_ID,
			ROUTEPOINT_ROUTE_ID, ROUTEPOINT_LONGITUDE, ROUTEPOINT_LATITUDE	};

	public static String[]		COLUMNS_IN_TIME_ENTRY						= new String[] { TIME_ID, TIME_ROUTE_ID,
			TIME_ROUTE_POINT_ID, TIME_JOG_ID, TIME_TIME, TIME_LAP			};

	public static final String	GET_LAST_ROUTE_POINT_FROM_JOG				= "SELECT time FROM time" + "WHERE _routeId = ("
																					+ "					SELECT _id FROM route "
																					+ "					WHERE _joId = 1 " + "			)"
																					+ "			ORDER BY time DESC LIMIT 1;";

	public static final String	GET_ALL_JOG_ENTRIES							= "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
																					+ "FROM 	jog "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
																					+ "	FROM 	time startTimes "
																					+ "	WHERE 	startTimes.time = ( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = startTimes.jogId "
																					+ "			ORDER BY jog.routePointId ASC "
																					+ "			LIMIT 1 "
																					+ "			) "
																					+ ") jogStartTimes "
																					+ "ON jog._id = jogStartTimes._jogId "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
																					+ "	FROM 	time stopTimes "
																					+ "	WHERE 	stopTimes.time = "
																					+ "		( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = stopTimes.jogId "
																					+ "			ORDER BY jog.routePointId DESC "
																					+ "			LIMIT 1 "
																					+ "		) "
																					+ ") jogStopTimes "
																					+ "ON jog._id = jogStopTimes._jogId";

	public static final String	FETCH_ALL_TODAYS_JOG_ENTRY_START_STOP_TIMES	= "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
																					+ "FROM 	jog "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
																					+ "	FROM 	time startTimes "
																					+ "	WHERE 	startTimes.time = ( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = startTimes.jogId "
																					+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
																					+ "			ORDER BY jog.routePointId ASC "
																					+ "			LIMIT 1 "
																					+ "			) "
																					+ ") jogStartTimes "
																					+ "ON jog._id = jogStartTimes._jogId "
																					+ "INNER JOIN "
																					+ "( "
																					+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
																					+ "	FROM 	time stopTimes "
																					+ "	WHERE 	stopTimes.time = "
																					+ "		( "
																					+ "			SELECT	jog.time "
																					+ "			FROM 	time jog "
																					+ "			WHERE 	jog.jogId = stopTimes.jogId "
																					+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
																					+ "			ORDER BY jog.routePointId DESC "
																					+ "			LIMIT 1 "
																					+ "		) "
																					+ ") jogStopTimes "
																					+ "ON jog._id = jogStopTimes._jogId";

	public static String		FETCH_ALL_TODAYS_JOG_ENTRIES				= "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
																					+ "FROM route "
																					+ "INNER JOIN ("
																					+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
																					+ "			FROM routePoint, route "
																					+ "			WHERE route._id = routePoint._routeId "
																					+ "		) routeandpoints "
																					+ "ON route._id = routeandpoints._routeId "
																					+ "INNER JOIN ("
																					+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
																					+ "			FROM time, route "
																					+ "			WHERE route._id = time.routeId "
																					+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'now') "
																					+ "		) timesroutesandpoints "
																					+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
																					+ "INNER JOIN ( "
																					+ "			SELECT jog._id AS _jogId, jog.name AS name "
																					+ "			FROM jog "
																					+ "		) jogdetails "
																					+ "ON route._jogId = jogdetails._jogId";

	public static String selectAJogEntry(String jogId) {
		return "  SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route"
				+ "			WHERE route._id = routePoint._routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ("
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog"
				+ "			WHERE jog._id = "
				+ jogId
				+ "		) jogdetails " + "ON route._jogId = jogdetails._jogId";
	}

	public static String	FETCH_STUBBEDOUT_JOG_ENTRIES	= "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap"
																	+ "FROM route "
																	+ "INNER JOIN ("
																	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude"
																	+ "			FROM routePoint, route"
																	+ "			WHERE route._id = routePoint._routeId "
																	+ "		) routeandpoints "
																	+ "ON route._id = routeandpoints._routeId "
																	+ "INNER JOIN ("
																	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId"
																	+ "			FROM time, route"
																	+ "			WHERE route._id = time.routeId "
																	+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'2008-03-10T10:00:00:00.000') "
																	+ "		) timesroutesandpoints "
																	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
																	+ "INNER JOIN ("
																	+ "			SELECT jog._id AS _jogId, jog.name AS name"
																	+ "			FROM jog"
																	+ "		) jogdetails "
																	+ "ON route._jogId = jogdetails._jogId";

	public static String getJogId(String jogName) {
		return "SELECT _id " + "FROM jog " + "WHERE name = " + jogName + "LIMIT 1";
	}

	public static String insertJogName(String jogName) {
		return "INSERT INTO jog (name) VALUES ( \'" + jogName + "\')";
	}

	public static String selectJustEnteredJogId() {
		return "SELECT _id " + "FROM jog " + "ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String insertRouteJogId(String jogId) {
		return "INSERT INTO route (_jogId) " + "VALUES ( \'" + jogId + "\');";
	}

	public static String selectJustEnteredRouteId(String jogId) {
		return "SELECT _id " + "FROM route " + "WHERE _jogId = " + jogId + " ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String selectJustEnteredRoutePointId(String routeId) {
		return "SELECT _id FROM routePoint WHERE _routeId = " + routeId + " ORDER BY _id DESC LIMIT 1";
	}

	public static String insertRoutePoint(String pointNo, String _routeId, String latitude, String longitude) {
		return "INSERT INTO routePoint (pointNo, _routeId, latitude, longitude ) VALUES ( \'" + pointNo + "\',\'" + _routeId
				+ "\',\'" + latitude + "\',\'" + longitude + "\')";
	}

	public static String addATimeEntry(String jogId, String routeId, String routePointId, String time, String lap) {
		return "INSERT INTO time (jogId, routePointId, routeId, time, lap) " + "VALUES (\'" + jogId + "\',\'" + routePointId
				+ "\',\'" + routeId + "\',\'" + time + "\',\'" + lap + "\');";
	}

	public static final String	WHERE_ENTRIES_WERE_TODAY		= "strftime( \"%Y-%m-%d\"," + TIME_TIME
																		+ ") = strftime( \"%Y-%m-%d\",'now');";

	public static final String	WHERE_ENTRIES_WERE_THIS_MONTH	= "strftime( \"%Y-%m\"," + TIME_TIME
																		+ ") = strftime( \"%Y-%m\",'now');";

	public static String whereEntriesWereThisMonth(Calendar calendar) {
		String query = "strftime( \"%Y-%m\"," + TIME_TIME + ") = strftime( \"%Y-%m\",'"
				+ DBHelperTools.isoFormat(calendar.getTimeInMillis()) + "');";

		return query;
	}

	// Entries must be entered into DB in YYYY-MM-DDTHH:MM:SS.SSS format
	public static String getWhereCondition(Integer day, Integer month, Integer year) {
		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		return "strftime( \"%Y-%m-%d\"," + TIME_TIME + ") = strftime( \"%Y-%m-%d\",\"" + year.toString() + "-" + monthString + "-"
				+ dayString + "T12:00:00.000\");";
	}

	public static String GET_ENTRIES(Integer day, Integer month, Integer year) {

		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m-%d\","
				+ TIME_TIME
				+ ") = strftime( \"%Y-%m-%d\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-"
				+ dayString
				+ "T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}

	public static String GET_MONTH_ENTRIES(Integer month, Integer year) {

		String monthString;

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m\","
				+ TIME_TIME
				+ ") = strftime( \"%Y-%m\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-01T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}

}
